package com.yao.excelanalyze.util;

import com.yao.excelanalyze.entity.BookObj;
import com.yao.excelanalyze.entity.Data;
import com.yao.excelanalyze.entity.SheetObj;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.*;

import static org.apache.commons.lang3.StringUtils.isBlank;

public class ExcelParser {

    private final Logger logger = LoggerFactory.getLogger(ExcelParser.class);

    public static final String OFFICE_EXCEL_XLS = "xls";
    public static final String OFFICE_EXCEL_XLSX = "xlsx";
    private String[] type = {"100K", "500K", "1M"};


    public void parse(String[] args) throws IOException, InvalidFormatException {
        List<Workbook> books = getBooks(args);
        logger.info("成功获取excel文件，size={}", books.size());
        List<BookObj> bookObjs = new ArrayList<>();
        logger.info("成功解析文件");
        for (int i = 0; i < books.size(); i++) {
            Workbook workbook = books.get(i);
            if (workbook!=null){
                BookObj bookObj = readBook(workbook, i);
                bookObjs.add(bookObj);
                workbook.close();
            }
        }
        // 解析数据
        BookObj book1 = bookObjs.get(0);
        BookObj book2 = bookObjs.get(1);
        BookObj book3 = bookObjs.get(2);
        XSSFWorkbook newBook = new XSSFWorkbook();
        book1.getSheetObjMap().forEach((k,v)->{
            if (book2.getSheetObjMap().containsKey(k)&&book3.getSheetObjMap().containsKey(k)){
                SheetObj sheetObj1 = book1.getSheetObjMap().get(k);
                SheetObj sheetObj2 = book2.getSheetObjMap().get(k);
                SheetObj sheetObj3 = book3.getSheetObjMap().get(k);
                // 三张表都有同一个sheet则创建一同名sheet记录结果
                Sheet sheet = newBook.createSheet(k);
                Row header = sheet.createRow(0);
                for (int i = 0; i < 5; i++) {
                    header.createCell(i * 4).setCellValue("feature");
                    header.createCell(i * 4 + 1).setCellValue("beta");
                    header.createCell(i * 4 + 2).setCellValue("newlabel2");
                    header.createCell(i * 4 + 3).setCellValue("unit");
                }
                compareAndWrite(sheet, sheetObj1, sheetObj2, sheetObj3);
            }
        });
        newBook.write(new FileOutputStream(args[3]));
        newBook.close();

    }

    public void compareAndWrite(Sheet sheet, SheetObj sheetObj1, SheetObj sheetObj2, SheetObj sheetObj3) {
        List<Data> dataList1 = sheetObj1.getDataList();
        List<Data> dataList2 = sheetObj2.getDataList();
        List<Data> dataList3 = sheetObj3.getDataList();
        Map<String, Set<Data>> rowData = new HashMap<>(16);
        logger.info("开始比较sheet[{}]",sheetObj1.getSheetName());
        dataList1.forEach(d1 -> {
            dataList2.forEach(d2 -> {
                doCompare(d1, d2, rowData);
            });
        });
        dataList1.forEach(d3 -> {
            dataList3.forEach(d4 -> {
                doCompare(d3, d4, rowData);
            });
        });
        dataList2.forEach(d5 -> {
            dataList3.forEach(d6 -> {
                doCompare(d5, d6, rowData);
            });
        });
        logger.info("分析完成，找到{}组对应关系", rowData.size());
        doWrite(rowData, sheet);
    }

    private void doCompare(Data data1, Data data2, Map<String, Set<Data>> data) {
        if (data1.getKey().equals(data2.getKey())) {
            updateMap(data1, data2, data, data1.getKey());
        }
    }

    private void doWrite(Map<String, Set<Data>> data, Sheet sheet) {
        logger.info("开始写入sheet[{}]",sheet.getSheetName());
        int i = 1;
        for(Map.Entry<String, Set<Data>> entry:data.entrySet()) {
            Row row = sheet.createRow(i);
            writeRow(row,entry.getValue());
            i++;
        }
    }

    private void writeRow(Row row, Set<Data> set) {
        Iterator<Data> iterator = set.iterator();
        int i = 0;
        while (iterator.hasNext()) {
            Data next = iterator.next();
            row.createCell(i * 4).setCellValue(next.getName());
            row.createCell(i * 4 + 1).setCellValue(next.getBeta());
            row.createCell(i * 4 + 2).setCellValue(next.getValue());
            row.createCell(i * 4 + 3).setCellValue(next.getType());
            i++;
        }
    }

    private void updateMap(Data data1, Data data2, Map<String, Set<Data>> data, String key) {
        if (data.containsKey(key)) {
            Set<Data> set = data.get(key);
            set.add(data1);
            set.add(data2);
        } else {
            Set<Data> set = new HashSet<>();
            set.add(data1);
            set.add(data2);
            data.put(key, set);
        }
    }

    private Workbook createWorkbook(String name) throws IOException, InvalidFormatException {
        FileInputStream fileOutputStream = new FileInputStream(new File(name));
        return WorkbookFactory.create(fileOutputStream);
    }


    private BookObj readBook(Workbook workbook, int type) {
        Map<String, SheetObj> sheetMap = new HashMap<>();
        int num = workbook.getNumberOfSheets();
        for (int i = 0; i < num; i++) {
            Sheet sheetAt = workbook.getSheetAt(i);
            if (sheetAt != null) {
                SheetObj sheetObj = readSheet(sheetAt, type);
                sheetMap.put(sheetAt.getSheetName().toLowerCase().trim(), sheetObj);

            }
        }
        return new BookObj(sheetMap);
    }

    private SheetObj readSheet(Sheet sheet, int t) {
        ArrayList<Data> data = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null && row.getPhysicalNumberOfCells() != 0) {
                Cell cell1 = row.getCell(0);
                if (cell1 != null) {
                    cell1.setCellType(CellType.STRING);
                    String name = cell1.getStringCellValue();
                    if (name != null && !name.equals("")) {
                        if (!name.contains("chr")) {
                            continue;
                        }
                        Cell cell2 = row.getCell(1);
                        cell2.setCellType(CellType.STRING);
                        String beta = cell2.getStringCellValue().trim();
                        Cell cell3 = row.getCell(2);
                        cell3.setCellType(CellType.STRING);
                        String value = cell3.getStringCellValue().trim();
                        String key = generateKey(name, value);
                        data.add(new Data(value, name, beta, key, type[t]));
                    }
                }
            }
        }
        return new SheetObj(sheet.getSheetName().toLowerCase().trim(), data);
    }

    private String generateKey(String name, String value) {
        String[] s = name.split("_");
        String[] split = value.split("\\.");
        String key = null;
        if (name.contains("random")) {
            key = s[0] + "#" + "random" + "#" + split[0].trim();
        } else {
            key = s[0] + "#" + split[0].trim();
        }
        return key;
    }

    private List<Workbook> getBooks(String[] args) throws IOException, InvalidFormatException {
        List<Workbook> workbooks = new ArrayList<>();
        if (args.length != 0) {
            for (int i = 0; i < 3; i++) {
                Workbook workbook = getWorkbook(args[i]);
                if (workbook != null) {
                    workbooks.add(workbook);
                }
            }
        }
        return workbooks;
    }


    private static Workbook getWorkbook(String filepath)
            throws EncryptedDocumentException, InvalidFormatException, IOException {
        InputStream is = null;
        Workbook wb = null;
        if (isBlank(filepath)) {
            throw new IllegalArgumentException("文件路径不能为空");
        } else {
            String suffiex = getSuffiex(filepath);
            if (isBlank(suffiex)) {
                throw new IllegalArgumentException("文件后缀不能为空");
            }
            if (OFFICE_EXCEL_XLS.equals(suffiex) || OFFICE_EXCEL_XLSX.equals(suffiex)) {
                try {
                    is = new FileInputStream(new File(filepath));
                    wb = WorkbookFactory.create(is);
                } finally {
                    if (is != null) {
                        is.close();
                    }
                    if (wb != null) {
                        wb.close();
                    }
                }
            } else {
                throw new IllegalArgumentException("该文件非Excel文件");
            }
        }
        return wb;
    }


    private static String getSuffiex(String filePath) {
        if (isBlank(filePath)) {
            return "";
        }
        int index = filePath.lastIndexOf(".");
        if (index == -1) {
            return "";
        }
        return filePath.substring(index + 1, filePath.length());
    }


    public static void main(String[] args) {
//        String s = "12.3";
//        String s1 = "12_3";
//        String[] split = s.split("\\.");
//        String[] s2 = s1.split("_");
//        System.out.println("lll");

    }
}
